/*
 * 
 */

package com.spartancoder.accommodation.models;

import com.spartancoder.accommodation.beans.ConflictBean;
import com.spartancoder.accommodation.db.Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

/**
 *
 * @author andrei
 */
public class Conflicts {

	private static Logger log = Logger.getLogger(Conflicts.class.getName());
	private static Database db = Database.getInstance();

	public static List<ConflictBean> find() {
		try {
			Connection conn = db.getConnection();

			String query = "select o.place_id, o.room, count(o.option_id) as number " +
					"from `options` o where o.session_id = (" +
						"select `session_id` " +
						"from `accommodation_sessions` " +
						"order by `start_date` desc limit 1)  " +
					"group by o.room " +
					"having number > (" +
						"select places_per_room " +
						"from `places` " +
						"where `place_id`=o.place_id)";
			PreparedStatement st = conn.prepareStatement(query);

			List<ConflictBean> ret = new LinkedList<ConflictBean>();
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				ConflictBean cb = new ConflictBean();
				cb.fill(rs);
				ret.add(cb);
			}
			
			return ret;
		} catch( SQLException ex ) {
			log.severe(ex.toString());
			throw new InternalError(ex.toString());
		}
	}
}
